I have requirement to create a SCSM report where I need to display "number of jobs completed by each Area/Category on a monthly basis" and "Time taken to complete".

I can able to get the count of number of jobs completed by each Area/Category but unable to find which field I need to select to get the "Time taken to complete" value or similar value from the SystemCenterServiceCatalogCube. Do we have any such field available from this cube? Please guide me if you have any info on this. Thanks.

Hi,

As far as I know there's no such value in the default cube. You need to calculate this value and set to some SR's field. If you are interested in calculating only the working time and you use the SR's Completion Time SLO, you could find this post useful.

If I've learned one thing, don't waste your time in the incredibly limited SCSM cube world and instead write SQL queries. As a start, here's I think the general idea of what you are after.

select sr.Id, sr.Area, sr.Title, sr.CreatedDate, sr.CompletedDate, DATEDIFF(hh, sr.CreatedDate, sr.CompletedDate) as 'Hours to Complete'
from servicerequestdimvw as sr
where sr.CompletedDate > '7-1-2015'
	and sr.Area like '%AccountManagement%'